<?php

namespace teach\service;

use Yii;

class Shops
{

    /**
     * 生成随机字符串
     * @param int $length
     * @return string
     */
    public function createNoncestr($length = 32)
    {
        $chars = "abcdefghijklmnopqrstuvwxyz0123456789";
        $str = "";
        for ($i = 0; $i < $length; $i++) {
            $str .= substr($chars, mt_rand(0, strlen($chars) - 1), 1);
        }
        return $str;
    }

    public function skuBatch($map)
    {
        return Yii::$app->db->createCommand()->batchInsert('mall_assemble_specs', ['pid', 'value', 'stock', 'alone_price','assemble_price', 'product_no', 'img', 'create_time', 'status'], $map)->execute();
    }

    /**
     * 获取订单(跟产品的连表)
     * @param $where
     * @param $page
     * @param int $pre_page
     * @param $state
     * @return array
     */
    public function orders($where,$page, $pre_page = 10, $state)
    {
        $count = $this->orderCount($where);
        $data = ['total' => 0, 'total_page' => 0, 'page' => 1, 'pre_page' => $pre_page, 'lists' => []];

        if ($count) {
            $total_page = ceil( $count / $pre_page );
            $offset = ( $page - 1 ) * $pre_page;
            $sql = "SELECT id, pid, uid, out_trade_no, total_fee, logistic_fee, name, phone, address, order_num, count, create_time, avatar_url, status, group_id, mode_id FROM mall_assemble_order WHERE {$where} ORDER BY create_time DESC LIMIT {$offset}, {$pre_page}";
            $res = Yii::$app->db->createCommand($sql)->queryAll();
            $resData = [];
            foreach ($res as $_key => $_val) {
                $productSql = "SELECT id, img, old_price, group_num FROM mall_assemble_product WHERE {$_val['pid']}";
                $product = Yii::$app->db->createCommand($productSql)->queryOne();
                $_val['pid'] = $product['id'];
                $_val['img'] = $product['img'];
                $_val['old_price'] = $product['old_price'];

                // 统计订单是否成团
                $numSql = "SELECT COUNT(*) FROM mall_assemble_order WHERE group_id = '{$_val['group_id']}'";
                $group_num = Yii::$app->db->createCommand($numSql)->queryScalar();
                $_val['group'] = $product['group_num'] - $group_num;

                //如果state = 6 把status 改成 11
                if ($state == 6) {
                    $_val['status'] = 11;
                }
                $resData[$_key] = $_val;
            }
            $data = ['total' => $count, 'total_page' => $total_page, 'page' => $page, 'pre_page' => $pre_page, 'lists' => $resData];
        }
        return $data;
    }

    /**
     * 统计订单总数
     * @param $where
     * @return false|null|string
     */
    private function orderCount($where)
    {
        $sql = "SELECT COUNT(*) FROM mall_assemble_order WHERE {$where}";
        return Yii::$app->db->createCommand($sql)->queryScalar();
    }

    /**
     * 查找一条订单
     * @param $where
     * @return array|false
     */
    public function orderOne($where)
    {
        $sql = "SELECT orders.id AS oid, orders.openid,orders.mid, orders.uid, orders.sku_id, orders.out_trade_no, orders.total_fee, orders.status AS order_status, pay.transaction_id, pay.status AS pay_status, pay.id AS p_id FROM mall_assemble_order AS orders LEFT JOIN mall_assemble_pay AS pay ON orders.out_trade_no = pay.out_trade_no WHERE {$where}";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 更新订单为确认退款或者拒绝退款
     * @param $map
     * @param $where
     * @return int
     */
    public function orderRefundStatus($map, $where)
    {
        $sql = "UPDATE mall_assemble_order SET {$map} WHERE {$where}";
        return Yii::$app->db->createCommand($sql)->execute();
    }

    /**
     * 退款后更记录状态 记录返回的信息
     * @param $pay_id
     * @param $oid
     * @param $out_trade_no
     * @param $result
     * @return bool
     */
    public function orderBatchStatus($pay_id, $oid, $out_trade_no, $result)
    {
        $db = Yii::$app->db;
        $transaction = $db->beginTransaction();
        try {
            //订单表
            $sqlTotal = "UPDATE mall_assemble_order SET status = -3 WHERE id = {$oid}";
            $db->createCommand($sqlTotal)->execute();
            //支付表记录分表
            $sqlList = "UPDATE mall_assemble_pay SET status = -2,info = '{$result}' WHERE id = {$pay_id}";
            $db->createCommand($sqlList)->execute();
            //积分记录表
            $sqlList = "UPDATE mall_bill SET status = -1 WHERE out_trade_no = '{$out_trade_no}'";
            $db->createCommand($sqlList)->execute();
            $transaction->commit();
            return true;
        } catch (\Exception $e) {
            $transaction->rollBack();
            return false;
        }
    }

    /**获取一条订单消息
     * @param $where
     * @return array|false
     */
    public function orderFindOne($where)
    {
        $sql = "SELECT * FROM mall_assemble_order WHERE {$where}";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 输入 | 修改 订单号 快递公司
     * @param $map
     * @param $where
     * @return int
     */
    public function orderExpress($map, $where)
    {
        $sql = "UPDATE mall_assemble_order SET {$map}  WHERE {$where}";
        return Yii::$app->db->createCommand($sql)->execute();
    }

    /**
     * 查找一条商家信息
     * @param $andWhere
     * @return array|false
     */
    public function storeOne($andWhere)
    {
        $sql = "SELECT * FROM edu_user WHERE {$andWhere}";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 获取一条申请退款
     * @param $where
     * @return array|false
     */
    public function refundOne($where)
    {
        $sql = "SELECT * FROM mall_refund_reason WHERE {$where}";
        return Yii::$app->db->createCommand($sql)->queryOne();
    }

    /**
     * 分页获取拼团产品
     * @param $where
     * @param $page
     * @param int $pre_page
     * @return array
     */
    public function productAssembleList($where, $page, $pre_page = 10)
    {
        $count = $this->productAssembleCount($where);
        $data = ['total' => 0, 'total_page' => 0, 'page' => 1, 'pre_page' => $pre_page, 'lists' => []];
        if ($count) {
            $offset = ($page - 1 ) * $pre_page;
            $total_page = ceil( $count / $pre_page);
            $sql = "SELECT * FROM mall_assemble_product WHERE {$where} ORDER BY create_time DESC LIMIT {$offset}, {$pre_page}";
            $res = Yii::$app->db->createCommand($sql)->queryAll();

            $resData = [];
            foreach ($res as $_key => $_val) {
                $sqlSku = "SELECT * FROM mall_assemble_specs WHERE pid = {$_val['id']}";
                $sku = Yii::$app->db->createCommand($sqlSku)->queryAll();
                $_val['sku'] = $sku;
                $resData[$_key] = $_val;
            }
            $data = ['total' => $count, 'total_page' => $total_page, 'page' => $page, 'pre_page' => $pre_page, 'lists' => $resData];
        }
        return $data;
    }

    /**
     * 拼单统计数量
     * @param $where
     * @return false|null|string
     */
    private function productAssembleCount($where)
    {
        $sql = "SELECT COUNT(*) FROM mall_assemble_product WHERE {$where}";
        return Yii::$app->db->createCommand($sql)->queryScalar();
    }

    /**
     * 将拼团产品改为statu = -1 删除状态
     * @param $where
     * @return int
     */
    public function productAssembleDelete($where)
    {
        $sql = "UPDATE mall_assemble_product SET status = -1 WHERE {$where}";
        return Yii::$app->db->createCommand($sql)->execute();
    }


}